package com.kushal.published.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

	public class ReadDataFromDB {

		static final String SQL_QUERY = "SELECT * FROM SANJAAL.RICHEST_400_AMR_2009";
		static String driver = "com.mysql.jdbc.Driver";
		static String url = "jdbc:mysql://localhost/generaldb";
		static String username = "root";
		static String password = "myPassword";

		/**
		 * This class will create and return a database connection.
		 */
		public static Connection getConnection() throws Exception {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, username, password);
			return conn;
		}

		public static void readData(String sql) throws Exception {
			Connection conn = null;
			conn = getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData rsMetaData = rs.getMetaData();

			int columnCount=rsMetaData.getColumnCount();
			//rsMetaData.getCol
			System.out.println("Column Counts: "+columnCount);
			String data="<table>\n";
			data+="\t<tr>\n"; //header row
			//prepare headers
			int headerIndex=1; //starts from 1
			while(headerIndex<=columnCount)
			{
				data+="\t\t<td halign=\"center\" valign=\"top\" width=\""
					+100/columnCount+"%\"><font color=\"red\">"+
					rsMetaData.getColumnName(headerIndex).toUpperCase()+
					"</font></td>\n";

				headerIndex++;
			}
			data+="\t<tr>\n"; //end of header row


			while (rs.next()) {
				data+="\t<tr>\n";

				/**Note that column index starts from 1 not 0 in resultset*/
				int columnIndex=1;
				while (columnIndex<=columnCount)
				{
					data+="\t\t<td halign=\"center\" valign=\"top\" width=\""+
					100/columnCount+"%\">"+rs.getString(columnIndex)+"</td>\n";


				columnIndex++;
				}
				data+="\t</tr>\n";

			}

			data+="</table>";

			System.out.println(data);


		}



		public static void main(String args[]) throws Exception {
			readData(SQL_QUERY);

		}

		/*
		 * SANJAAL CORPS MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF
		 * THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
		 * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
		 * PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SANJAAL CORPS SHALL NOT BE LIABLE FOR
		 * ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
		 * DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
		 *
		 * THIS SOFTWARE IS NOT DESIGNED OR INTENDED FOR USE OR RESALE AS ON-LINE
		 * CONTROL EQUIPMENT IN HAZARDOUS ENVIRONMENTS REQUIRING FAIL-SAFE
		 * PERFORMANCE, SUCH AS IN THE OPERATION OF NUCLEAR FACILITIES, AIRCRAFT
		 * NAVIGATION OR COMMUNICATION SYSTEMS, AIR TRAFFIC CONTROL, DIRECT LIFE
		 * SUPPORT MACHINES, OR WEAPONS SYSTEMS, IN WHICH THE FAILURE OF THE
		 * SOFTWARE COULD LEAD DIRECTLY TO DEATH, PERSONAL INJURY, OR SEVERE
		 * PHYSICAL OR ENVIRONMENTAL DAMAGE ("HIGH RISK ACTIVITIES"). SANJAAL CORPS
		 * SPECIFICALLY DISCLAIMS ANY EXPRESS OR IMPLIED WARRANTY OF FITNESS FOR
		 * HIGH RISK ACTIVITIES.
		 */

	}
